module.exports = {
    /* user module sql */
    login: "SELECT u_id,pwd FROM user WHERE username = #username",
    modifyPwd: "UPDATE user SET pwd = #newPwd WHERE pwd = #oldPwd and u_id = #id",

    /* project module sql */
    findProjectList: 'SELECT * from project WHERE ' +
    'p_id = #id and p_name LIKE %#name% and project_pm = #project_pm and ' +
    '#start_date and #end_date and status = #status and status <> \'inactive\'',
    updateProject: 'UPDATE project SET status = #status , p_name = #p_name , ' +
    'start_date=#start_date , end_date=#end_date , ' +
    'project_pm=#project_pm , project_leader=#project_leader WHERE p_id = #id',
    prjBatchDelete: 'UPDATE project SET status = \'inactive\' WHERE p_id = #id',
    insertProject: 'INSERT INTO project (`p_name`,`status`, `start_date`, `end_date`,  `project_pm`, `project_leader`)' +
    ' values(#p_name , #status , #start_date , #end_date , #project_pm , #project_leader)',

    /* member module sql */
    findMemberList: 'SELECT * from user WHERE ' +
    'u_id = #id and u_name LIKE %#name% and username LIKE %#username% and manager = #leader ' +
    'pwd = #pwd and team=#team and role = #role and role <> \'admin\' and status <> \'inactive\' ORDER BY u_id',
    updateMember: 'UPDATE user SET role = #role, team = #team , pwd = #pwd , email = #email , manager = #leader WHERE u_id = #id',
    memberBatchDelete: 'UPDATE user SET status = \'inactive\' WHERE u_id = #id',
    insertMember: 'INSERT INTO user (`username`, `pwd`, `u_name`, `role`, `manager`, `email`) ' +
    'values(#username , #pwd , #u_name , #role , #leader , #email)',
    insertMemberNoManager: 'INSERT INTO user (`username`, `pwd`, `u_name`, `role`, `email`) ' +
    'values(#username , #pwd , #u_name , #role , #email)',

    /* plan module sql */
    findPlan: 'SELECT * FROM plan WHERE pl_id = #plan_id and start_date >= #start_date and ' +
    'end_date <= #end_date and status = #status order by pl_id DESC',
    findPlanList: 'SELECT * FROM plan WHERE #start_date and #end_date order by pl_id DESC',
    addPlan: 'INSERT INTO plan (`pl_name`, `start_date`, `end_date`, `next_plan` , `updatedAt`) VALUES ' +
    '(#plan_name , #start_date , #end_date , #next_plan , #updatedAt);',
    editPlan: 'UPDATE plan SET pl_name = #pl_name , start_date = #start_date , end_date = #end_date , ' +
    'next_plan = #next_plan , updatedAt = #updatedAt , status = #status WHERE pl_id = #id',

    /* plan detail sql */
    findPlanDetail: 'SELECT fk_plan,percentage,fk_prj,extra,fk_usr,pd_id FROM plan_detail,plan WHERE pd_id = #id and ' +
    'fk_plan = #plan_id and fk_pm = #pm_id and fk_prj = #p_id and status = #status and pl_id = fk_plan',
    addPlanDetail: 'INSERT INTO plan_detail (`fk_plan`, `fk_pm`, `fk_prj`, `fk_usr`, `percentage`, `extra`) VALUES ' +
    '(#pl_id , #pm_id , #p_id , #u_id , #percentage , #extra);',
    addPlanDetailNoExtra: 'INSERT INTO plan_detail (`fk_plan`, `fk_pm`, `fk_prj`, `fk_usr`, `percentage`) VALUES ' +
    '(#pl_id , #pm_id , #p_id , #u_id , #percentage);',

    /* plan extra sql */
    findPlanExtra: 'SELECT type,content,pe_id FROM plan_extra,plan WHERE pe_id = #id and fk_plan = #plan_id and ' +
    'fk_pm = #pm_id and type = #type and pl_id = fk_plan and status = #status',
    addPlanExtra: 'INSERT INTO plan_extra (`fk_plan`, `fk_pm`, `content`, `type`) VALUES ' +
    '(#plan_id , #pm_id , #content , #type);',

    /* plan detail view sql */
    findPlanDetailByProject: 'SELECT * FROM statistics_project WHERE fk_plan = #plan_id and fk_prj = #project',
    findPlanDetailByManager: 'SELECT * FROM statistics_manager WHERE fk_plan = #plan_id and fk_pm = #pm',

    /* other sql */
    findSetting: "SELECT * from setting WHERE s_id = 1",
    updateSetting: 'UPDATE setting SET on_off = #status , freq = #freq , name_tpl = #name_tpl , ' +
    'next_plan_tpl = #next_tpl WHERE s_id = 1',
    deleteAllCurrent: "DELETE FROM plan_detail WHERE fk_plan = #plan_id and fk_pm = #pm_id",
    deleteAllCurrentExtra: "DELETE FROM plan_extra WHERE fk_plan = #plan_id and fk_pm = #pm_id",

    findAllTeamAndMember: "SELECT * FROM team,user WHERE user.team = team.t_name ORDER BY t_name",
    findAllTeams: "SELECT * FROM team WHERE status <> 'inactive' ORDER BY t_name",
    findTeam : "SELECT * FROM team WHERE t_name = #t_name",
    updateTeam: "UPDATE team SET leader = #leader,description = #description WHERE t_name = #t_name",
    addTeam: "INSERT INTO team(t_name,description) VALUES (#t_name,#description)"
};